#!/usr/bin/env python
# -*- coding: UTF-8 -*-

################################################################################
#
# Copyright (c) 2020 openEuler.org, Inc. All Rights Reserved
#
################################################################################
"""
1. When executing SQL with parameters, please use sql
statement to specify the list of conditions that
need to be entered, and then use tuple/list for conditional batch allocation
2. There is no need to use quotation marks to specify the data type in the format SQL,
and the system will automatically recognize it according to the input parameters
3. There is no need to use conversion function in the input
value, the system will automatically handle it

Authors: xiaojianghui
Date:    10/22/2020 11:01 AM
"""
import pymysql
from pymysql import cursors
from DBUtils import PooledDB
from dbConnecttion import Config


class Mysql(object):
    """
        Connection pool object
    """
    __pool = None

    def __init__(self):
        """
            init object
        """
        self.__conn = Mysql.__getConn()
        self.__cursor = self.__conn.cursor()

    @staticmethod
    def __getConn():
        """
            Static method, remove the connection from the connection pool
        :return MySQLdb.connection
        """
        if Mysql.__pool is None:
            __pool = PooledDB.PooledDB(creator=pymysql, mincached=1,
                                       maxcached=20, host=Config.DBHOST,
                                       port=Config.DBPORT,
                                       user=Config.DBUSER,
                                       passwd=Config.DBPWD, db=Config.DBNAME,
                                       use_unicode="UTF-8", charset=Config.DBCHAR,
                                       cursorclass=cursors.DictCursor)
            return __pool.connection()
        return None

    def getOne(self, sql, param=None):
        """
        Execute the query and take out the first
       :param sql:Query SQL. If there are query conditions, please specify
       only the condition list and pass in the condition value using the parameter [param]
       :param param:Optional parameter, condition list value (tuple/list)
       :return:result list/boolean Query result set
       """
        if param is None:
            count = self.__cursor.execute(sql)
        else:
            count = self.__cursor.execute(sql, param)
        if count > 0:
            result = self.__cursor.fetchone()
        else:
            result = False
        return result

    def getMany(self, sql, param=None):
        """
            Execute the query and take out num results
        :param sql:Query SQL. If there are query conditions, please specify
        only the condition list and pass in the condition value using the parameter [param]
        :param param:Optional parameter, condition list value (tuple/list)
        :return:result list/boolean Query result set
        """
        if param is None:
            count = self.__cursor.execute(sql)
        else:
            count = self.__cursor.execute(sql, param)
        if count > 0:
            result = self.__cursor.fetchall()
        else:
            result = False
        return result

    def insertOne(self, sql, value):
        """
            Insert a record into the data table
        :param sql:SQL format to be inserted
        :param value:Record data to be inserted tuple/list
        :return:insertId Number of rows affected
        """
        self.__cursor.execute(sql, value)
        return self.__getInsertId()

    def __getInsertId(self):
        """
            Get the id generated by the last insert operation
            of the current connection, or 0 if not
        """
        self.__cursor.execute("SELECT @@IDENTITY AS id")
        result = self.__cursor.fetchall()
        return result[0]['id']

    def __query(self, sql, param=None):
        """
            query data table records
        :param sql:SQL format and conditions, use (%s,%s)
        :param param:The value to be updated tuple/list
        :return:count Number of rows affected
        """
        if param is None:
            count = self.__cursor.execute(sql)
        else:
            count = self.__cursor.execute(sql, param)
        return count

    def update(self, sql, param=None):
        """
            Update data table records
        :param sql:SQL format and conditions, use (%s,%s)
        :param param:The value to be updated tuple/list
        :return:count Number of rows affected
        """
        return self.__query(sql, param)

    def delete(self, sql, param=None):
        """
            Delete data table record
        :param sql:SQL format and conditions, use (%s,%s)
        :param param:Condition to be deleted Value tuple/list
        :return:count Number of rows affected
        """
        return self.__query(sql, param)

    def begin(self):
        """
            Open transaction
        """
        self.__conn.autocommit(0)

    def end(self, option='commit'):
        """
            End the transaction
        """
        if option == 'commit':
            self.__conn.commit()
        else:
            self.__conn.rollback()

    def dispose(self, is_end=1):
        """
            Commit transaction
        """
        if is_end == 1:
            self.end('commit')
        else:
            self.end('rollback')

    def close(self):
        """
            Close the connection
        """
        self.__cursor.close()
        self.__conn.close()
